package toko.service;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import toko.domain.Buku;
import toko.domain.Penjualan;
import toko.domain.PenjualanDetail;
import java.util.Date;


public class TokoServiceJDBC implements TokoService {
    private static final String sqlInsert="INSERT INTO produk (kode, judul, pengarang, penerbit, harga)" +
	"VALUES (?,?,?,?,?)";
	private static final String sqlInsertPenjualan="INSERT INTO penjualan (waktu, kasir, pembayaran)" +
	"VALUES (?,?,?)";
	private static final String sqlIdPenjualan="select max(id) from penjualan";
	
	private static final String sqlInsertPenjualanDetail="INSERT INTO penjualan_detail (id_penjualan, id_buku, qty)" +
	"VALUES (?,?,?)";
	private static final String sqlSelectId="SELECT * FROM produk WHERE id=? ";
	private static final String sqlSelectKode="SELECT * FROM produk WHERE kode like ?";
	private static final String sqlSelectJudul="SELECT * FROM produk WHERE judul like ?";
	private static final String sqlDeleteId="DELETE FROM produk WHERE kode ?";

	private Connection connection;
	private PreparedStatement psInsert;
	private PreparedStatement psInsertPenjualan;
	private PreparedStatement psInsertPenjualanDetail;
	private PreparedStatement psIdPenjualan;
	private PreparedStatement psSelectById;
	private PreparedStatement psSelectByKode;
	private PreparedStatement psSelectByJudul;
	private PreparedStatement psDeleteById;
        private PreparedStatement psSelectByDate;
    private PenjualanDetail pd;
    private String DateMulai;
    private String date;



    public TokoServiceJDBC(Connection connection) throws Exception{
        this.connection = connection;
        this.psInsert = connection.prepareStatement(sqlInsert);
        this.psInsertPenjualan = connection.prepareStatement(sqlInsertPenjualan);
        this.psIdPenjualan = connection.prepareStatement(sqlIdPenjualan);
        this.psInsertPenjualanDetail = connection.prepareStatement(sqlInsertPenjualanDetail);
        this.psSelectById =connection.prepareStatement(sqlSelectId);
        this.psSelectByKode =connection.prepareStatement(sqlSelectKode);
        this.psSelectByJudul =connection.prepareStatement(sqlSelectJudul);
        this.psDeleteById =connection.prepareStatement(sqlDeleteId);
	}

	@Override
	public void simpan(Buku buku) {
            // TODO Auto-generated method stub
            try{
                psInsert.setString(1, buku.getKode());
                psInsert.setString(2, buku.getJudul());
                psInsert.setString(3, buku.getPengarang());
                psInsert.setString(4, buku.getPenerbit());
                psInsert.setBigDecimal(5, buku.getHarga());
                psInsert.executeUpdate();
            }catch (SQLException e){
                e.printStackTrace();
            }
	}

	/*@Override
	public void simpanPenjualanDetail(PenjualanDetail penjualanDetail) {
		// TODO Auto-generated method stub
		try{
			psInsertPenjualanDetail.setInt(1,penjualanDetail.getPenjualan());
			psInsertPenjualanDetail.setInt(2, penjualanDetail.getBuku().getId());
			psInsertPenjualanDetail.setInt(3, penjualanDetail.getQuantity());
			psInsertPenjualanDetail.executeUpdate();
		}catch (SQLException e){
			e.printStackTrace();
		}
	}*/

	@Override
	public void simpanPenjualan(Penjualan penjualan) {
            // TODO Auto-generated method stub
            try{
                connection.setAutoCommit(false);
                psInsertPenjualan.setDate(1,new java.sql.Date(penjualan.getWaktu().getTime()));
                psInsertPenjualan.setString(2, penjualan.getKasir());
                psInsertPenjualan.setBigDecimal(3, penjualan.getPembayaran());
                psInsertPenjualan.executeUpdate();

                ResultSet rs = connection.createStatement().executeQuery("SELECT LAST_INSERT_ID()");
                if(!rs.next()) throw new IllegalStateException("ID ga d temuin");
                Integer id = rs.getInt(1);

                for(PenjualanDetail d : penjualan.getDaftarPenjualanDetail()){
                    psInsertPenjualanDetail.setInt(1,id);
                    psInsertPenjualanDetail.setInt(2, d.getBuku().getId());
                    psInsertPenjualanDetail.setInt(3,d.getQuantity());
                    psInsertPenjualanDetail.executeUpdate();
                }
                /*
                 Penjualan p = new Penjualan();

            p.setKasir("Endy");
            p.setPembayaran(new BigDecimal(150000));
            p.setWaktu(new Date());
            p.getDaftarPenjualanDetail().add(pd);
                 */
                    connection.commit();
		}catch (SQLException e){
			e.printStackTrace();
		}
	}

	@Override
	public void hapus(Buku buku) {
            // TODO Auto-generated method stub
            try{
                psDeleteById.setString(1, buku.getKode());
                psDeleteById.executeQuery();
            }catch (SQLException e){
                e.printStackTrace();
            }
	}

	@Override
	public void importCSV(String filename) {
            try {
                connection.setAutoCommit(false);
                BufferedReader br = new BufferedReader(new FileReader(new File(filename)));
                String line;
                while ((line = br.readLine()) != null) {
                    String[] lines = line.split(";");
                    psInsert.setString(1, lines[0]);
                    psInsert.setString(2, lines[1]);
                    psInsert.setString(3, lines[2]);
                    psInsert.setString(4, lines[3]);
                    psInsert.setBigDecimal(5,new BigDecimal(lines[4]));
                    psInsert.executeUpdate();
                }
                    br.close();
                    connection.commit();
		} catch (Exception e) {
                    try{
                        System.out.println("Roll back, terjadi error");
                        connection.rollback();
                    }catch (SQLException e1){
				e.printStackTrace();
			}
		}finally{
                    try{
                        connection.setAutoCommit(true);
                    }catch (SQLException e2) {
                        // TODO: handle exception
                        e2.printStackTrace();
                    }
		}
	}

	@Override
	public Buku cariId(Integer id) {
            // TODO Auto-generated method stub
            try{
                psSelectById.setInt(1,id);
                ResultSet rs =psSelectById.executeQuery();

                //kalau next false, berarti tidak ada record yang ditemukan
                if(!rs.next()) return null;

                Buku b = konversiResultSetMenjadiBuku(rs);
                return b;
            }catch (Exception e){
                e.printStackTrace();
                return null;
            }
	}

	private Buku konversiResultSetMenjadiBuku(ResultSet rs) throws Exception{
            // TODO Auto-generated method stub
            Buku b = new Buku();
            b.setId(rs.getInt("id"));
            b.setKode(rs.getString("kode"));
            b.setJudul(rs.getString("judul"));
            b.setPenerbit(rs.getString("penerbit"));
            b.setPengarang(rs.getString("pengarang"));
            b.setHarga(rs.getBigDecimal("harga"));
            return b;
	}

	@Override
	public List<Buku> cariKode(String kode) {
            // TODO Auto-generated method stub
            try{
                List<Buku> hasil = new ArrayList<Buku>();
                psSelectByKode.setString(1, "%"+kode+"%");
                ResultSet rs =psSelectByKode.executeQuery();
                while(rs.next()){
                    Buku b = konversiResultSetMenjadiBuku(rs);
                    hasil.add(b);
                }
                    return hasil;
            }catch (Exception e){
                e.printStackTrace();
            }
		return null;
	}

	@Override
	public List<Buku> cariJudul(String judul) {
            // TODO Auto-generated method stub
            try{
                List<Buku> hasil = new ArrayList<Buku>();
                psSelectByJudul.setString(1, "%"+judul+"%");
                ResultSet rs =psSelectByJudul.executeQuery();
                while(rs.next()){
                    Buku b = konversiResultSetMenjadiBuku(rs);
                    hasil.add(b);
                }
                    return hasil;
            }catch (Exception e){
                e.printStackTrace();
            }
		return null;
	}
        @Override
        public List<Penjualan> cariPenjualan(Date mulai, Date sampai){
            // TODO Auto-generated method stub
	try{
		List<Penjualan> daftar = new ArrayList<Penjualan>();
		psSelectByDate.setString(1, "%"+date+"%");
		ResultSet rs = psSelectByDate.executeQuery();
		while(rs.next()){
			Penjualan p = konversiResultSetMenjadiPenjualan(rs);
			daftar.add(p);
		}
			return daftar;
		} catch (Exception e){
			e.printStackTrace();
		}
			return null;
		}

    private Penjualan konversiResultSetMenjadiPenjualan(ResultSet rs) {
        throw new UnsupportedOperationException("Not yet implemented");
    }
 

    public Integer jumlahBukuTerjual(Buku b, Date mulai, Date sampai) {
        throw new UnsupportedOperationException("Not supported yet.");
    }



}


private Buku konversiResultSetMenjadiBuku(ResultSet rs) throws Exception{
            // TODO Auto-generated method stub
            Buku b = new Buku();
            b.setId(rs.getInt("id"));
            b.setKode(rs.getString("kode"));
            b.setJudul(rs.getString("judul"));
            b.setPenerbit(rs.getString("penerbit"));
            b.setPengarang(rs.getString("pengarang"));
            b.setHarga(rs.getBigDecimal("harga"));
            return b;
	}